CREATE FUNCTION [dbo].[asi_GivingStatistics]( @Id varchar(10),
@StatReq varchar(10) )
--
-- Returns key giving Statistics for the contact identified by @Id
-- the statistic required are identified by the @StatReq parameter
-- which can be one of:
--
--  AVG - average gift value
--  MIN - Minimum gift amout
--  MAX - Maximum gift amount
--  COUNT - Count of gifts made
--  SUM - Sum of gift made
--
-- This is used by Giving Summary display
--
-- The calculations are based on vGift but due to the fact that multiple rows can
-- be returned for one original gift (e.g. in the case of reversals) this function
-- is needed as SQL Server does not allow an aggregate function (e.g. MIN) on an
-- expression containing an aggregate or a subquery (e.g. SUM(amount))
--
RETURNS money AS
BEGIN

DECLARE @Result money
SET @Result = 0

DECLARE @Gifts TABLE
(
  [OriginalTransaction] int,
  [Amount] money  
)

INSERT INTO @Gifts( [OriginalTransaction], [Amount]  )
SELECT [OriginalTransaction], SUM([Amount])
FROM [vGift] GH1 WITH(NOLOCK)
WHERE [ID] =  @Id
GROUP BY [OriginalTransaction]

IF @StatReq = 'AVG'
BEGIN
  SELECT @Result = (SELECT AVG([Amount] ) FROM @Gifts WHERE [Amount] <> 0 )
END

IF @StatReq = 'MIN'
BEGIN
  SELECT @Result = (SELECT MIN(ISNULL([Amount],0) ) FROM @Gifts WHERE [Amount] <> 0 )
END

IF @StatReq = 'MAX'
BEGIN
  SELECT @Result = (SELECT MAX([Amount] ) FROM @Gifts WHERE [Amount] <> 0 )
END

IF @StatReq = 'COUNT'
BEGIN
  SELECT @Result = (SELECT COUNT( * ) FROM @Gifts WHERE [Amount] <> 0 )
END

IF @StatReq = 'SUM'
BEGIN
  SELECT @Result = (SELECT SUM([Amount] ) FROM @Gifts)
END

RETURN @Result
END



GO